# count1 = 0
# count2 = 0
# class tools():
#
#
#     def __init__(self,name,name1):
#         self.name = name
#         self.name1 = name1
#         self.count1 = 0
#         self.count2 = 0
#     def count(self):
#
#         if self.name == "镰刀" or self.name1 == "斧头":
#             # global count1
#             self.count1+=1
#             print(f"使用了{self.count1}次镰刀")
#         if self.name == "斧头"or self.name1 == "镰刀":
#             # global count2
#             self.count2+=1
#             print(f"使用了{self.count2}次斧头")
# t1 = tools("镰刀","斧头")
# t1.count()
# t1.count()
# t1.count()
# import pymysql
# class call_the_roll():
#     def __init__(self,host,user,password,database,cursorclass=None):
#         self.host = host
#         self.user = user
#         self.password = password
#         self.database = database
#         self.cursorclass = cursorclass
#
#     def connect(self):
#         # self.db = pymysql.connect(host=self.host,user=self.user,
#         #                         password=self.password,database = self.datebase,cursorclass=self.cursorclass)
#         # self.cursor = self.db.cursor()
#
#         self.db = pymysql.connect(host=self.host, user=self.user,
#                                   password=self.password, database=self.database, cursorclass=self.cursorclass)
#         self.cursor = self.db.cursor()  # todo：创建游标， 对数据库有真实的增删改的时候，需要提交数据库和游标
#         # 游标，对数据库有增、删、改的时候，需要提交游标还有数据库对象，不然无效
#         # 查询的时候不需要提交游标
#     def close(self):
#         self.cursor.close()
#         self.db.close()
#     def __edit(self,sql):
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             self.db.commit()
#             result = "数据库更新成功"
#             self.close()
#         except Exception as e:
#             result = "数据库更新失败:%s" % e
#             print("select error %s" % e)
#             print(result)
#         return result
#     def select_all(self,sql):
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             result = self.cursor.fetchall()
#             self.close()
#         except Exception as e:
#             print("select error %s" % e)
#             result = None
#         return result
#     def insert(self,sql):
#         return self.__edit(sql)
#     def delete(self,sql):
#         return self.__edit(sql)
#     def update(self,sql):
#         return self.__edit(sql)
#     def call(self,sql):
#         import random
#         random_number = random.choice(sql)
#
#         return self.__edit(sql)
#
#         print(random_number)
#
# db1 = call_the_roll(host = "116.62.55.124",user="root",password="Aa123456!",database="test",cursorclass=pymysql.cursors.DictCursor)

# db1.insert("insert into test values('小天才',45,'盛京',123);")
# print(db1.select_all("select * from test;"))
#db1.delete("delete from student where id =1;")
# db1.update("update student set id = 1 where id =2;")
# db1.insert("insert into test values('小天才',18,'盛京',123);")
# db1.update("update test set adress = '沈阳' where adress = '盛京';")
# print(db1.select_all("select * from test;"))
# print(db1.select_all("select * from test;"))
# db1.delete("delete from test where age = 45")
# print(db1.select_all("select * from test;"))
# (db1.call("select name from test;")


# import pymysql
# import random
#
# class StudentManagementSystem:
#     def __init__(self, host, user, password, database):
#         self.host = host
#         self.user = user
#         self.password = password
#         self.database = database
#
#     def connect(self):
#         self.db = pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.database)
#         self.cursor = self.db.cursor()
#
#     def close(self):
#         self.cursor.close()
#         self.db.close()
#
#     def insert_student(self, student_id, student_name, english_score, math_score, chinese_score, class_code):
#         self.connect()
#         sql = "INSERT INTO test1 (student_id, student_name, english_score, math_score, chinese_score, class_code) VALUES ('%s', '%s', %d, %d, %d, '%s')"
#         try:
#             self.cursor.execute(sql % (student_id, student_name, english_score, math_score, chinese_score, class_code))
#             self.db.commit()
#             result = "学生信息插入成功"
#         except Exception as e:
#             result = "学生信息插入失败：%s" % e
#             print("insert error %s" % e)
#         self.close()
#         return result
#
#     def generate_random_score(self):
#         score_range = {'优秀': (90, 100), '良好': (80, 89), '中等': (70, 79), '及格': (60, 69), '不及格': (0, 59)}
#         grade_list = list(score_range.keys())
#         grades = random.choices(grade_list, weights=[10, 30, 30, 20, 10], k=1)  # 按比例随机选择成绩等级
#         score_range = score_range[grades[0]]
#         score = random.randint(score_range[0], score_range[1])
#         return score
#
#     def generate_random_class(self):
#         class_list = ['1809A', '1809B', '1810A', '1810B']
#         return random.choice(class_list)
#
#     def generate_students(self, num_of_students):
#         for i in range(1, num_of_students + 1):
#             student_id = str(i).zfill(4)  # 将学生ID填充为4位数，例如 1 -> 0001
#             student_name = f"Student {i}"
#             english_score = self.generate_random_score()
#             math_score = self.generate_random_score()
#             chinese_score = self.generate_random_score()
#             class_code = self.generate_random_class()
#             result = self.insert_student(student_id, student_name, english_score, math_score, chinese_score, class_code)
#             print(result)
#
# db = StudentManagementSystem(host="116.62.55.124", user="root", password="Aa123456!", database="test1")
# db.generate_students(100)



# import pymysql
# # pip install pymysql
# class DB():
#     def __init__(self,host,user,password,database,cursorclass = None):
#         """
#         :param host: 就是阿里云服务器的ip地址
#         :param user: 数据库的账号名  一般都是root
#         :param password: 数据库密码
#         :param database: 数据库的名字
#         :param cursorclass:  返回的格式是字典格式，有key和value
#         """
#         self.host = host
#         self.user = user
#         self.password = password
#         self.databases = database
#         self.cursorclass = cursorclass
#     def connect(self):
#         self.db = pymysql.connect(host = self.host,user = self.user,
#                                   password = self.password,database = self.databases,cursorclass=self.cursorclass)
#         self.cursor = self.db.cursor() # todo：创建游标， 对数据库有真实的增删改的时候，需要提交数据库和游标
#         # 游标，对数据库有增、删、改的时候，需要提交游标还有数据库对象，不然无效
#         # 查询的时候不需要提交游标
#
#     def close(self):
#         self.cursor.close() # 关闭游标
#         self.db.close() # 关闭数据库
#     def select_one(self,sql): # select * from student where id = 3;   10
#         self.connect() # 第一步，调用连接数据库方法，连接数据库
#         try: # 尝试去执行sql
#             self.cursor.execute(sql) # 使用游标对象，执行sql语句
#             result = self.cursor.fetchone() # 返回一条数据 fetchone()
#             self.close() # 最后关闭掉数据库，不要忘记
#         except Exception as e:  # 如果sql执行错误
#             print("select error %s"%e)
#             result = None
#         return result
#     def select_all(self,sql): #  select * from student where id = 3; 10
#         self.connect()
#         try:
#             self.cursor.execute(sql)
#             result = self.cursor.fetchall()  # 返回一条数据
#             self.close()
#         except Exception as e:
#             print("select error %s" % e)
#             result = None
#         return result
#     def __edit(self,sql): # 相当于是一个主程序
#         self.connect() # 先连接数据库
#         try:
#             self.cursor.execute(sql) # 使用游标对象，执行sql语句
#             self.db.commit() # 数据库对象提交
#             result = "数据库更新成功"
#         except Exception as e:
#             result = "数据库更新失败%s"%e
#         return result
#     def insert(self,sql): # insert into student(3,11,55);
#         return self.__edit(sql)
#     def delete(self,sql): # delete from student where id = 3;
#         return self.__edit(sql)
#     def update(self,sql): # update student set id = 3 where id =5;
#         return self.__edit(sql)
#
# # cursorclass =
#
# db1 = DB(host = "47.104.65.165",user="root",password="123456",database = "test2",cursorclass = pymysql.cursors.DictCursor)
# db1.insert("insert into test2(图书名，作者名称，日期) values('小天才','小天才1','2020-01-02');")
#db1.delete("delete from student where id =1;")
# db1.update("update student set id = 1 where id =2;")
# print(db1.select_one('select * from student;'))
import pymysql

class BookManagementSystem:
    def __init__(self, host, user, password, database,cursorclass = None):
        self.host = host
        self.user = user
        self.password = password
        self.databases = database
        self.cursorclass = cursorclass
        self.db = pymysql.connect(host=self.host, user=self.user, password=self.password, database=self.databases, cursorclass=pymysql.cursors.DictCursor)

    def add_book(self, book_name, author, date):
        sql = f"INSERT INTO books (图书名, 作者名称, 日期) VALUES ('{book_name}', '{author}', '{date}')"
        return self.db.insert(sql)

    def edit_book(self, book_id, book_name, author, date):
        sql = f"UPDATE books SET 图书名 = '{book_name}', 作者名称 = '{author}', 日期 = '{date}' WHERE id = {book_id}"
        return self.db.update(sql)

    def delete_book(self, book_id):
        sql = f"DELETE FROM books WHERE id = {book_id}"
        return self.db.delete(sql)

    def view_all_books(self):
        sql = "SELECT * FROM books"
        return self.db.select_all(sql)

    def view_book_by_id(self, book_id):
        sql = f"SELECT * FROM books WHERE id = {book_id}"
        return self.db.select_one(sql)

    def view_book_by_name(self, book_name):
        sql = f"SELECT * FROM books WHERE 图书名 = '{book_name}'"
        return self.db.select_all(sql)

    def view_books_by_keyword(self, keyword):
        sql = f"SELECT * FROM books WHERE 图书名 LIKE '%{keyword}%' OR 作者名称 LIKE '%{keyword}%'"
        return self.db.select_all(sql)

# 创建图书管理系统对象
bms = BookManagementSystem(
    host="47.104.65.165",
    user="root",
    password="123456",
    database="test2"
)

# 新增图书
result = bms.add_book("小天才", "小天才1", "2020-01-02")
print(result)

# 修改图书
result = bms.edit_book(1, "新图书", "新作者", "2023-08-28")
print(result)

# 删除图书
result = bms.delete_book(1)
print(result)

# 查看所有图书
books = bms.view_all_books()
print(books)

# 根据图书ID查看图书
book = bms.view_book_by_id(1)
print(book)

# 根据图书名查看图书
books = bms.view_book_by_name("小天才")
print(books)

# 根据关键字查找图书
books = bms.view_books_by_keyword("天才")
print(books)